﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Threading.Tasks;
using System.Data.SqlClient;
using DAL;

namespace BLL
{
    public class ForBorrowStatistics
    {
        //查询借阅总人数
        public string ShowBorrowReader()
        {
            //借阅总人数
            string sqlSum = @"select COUNT(*) from Reader where ReaderID in (select ReaderID from Borrow)";
            //执行
            int sum = Convert.ToInt32(DBHelper.ExecuteScalar(sqlSum));
            if (sum != -1)
            {
                //显示图书总计
                return string.Format("共{0}人", sum.ToString());
            }
            else
            {
                return "查询异常";
            }
        }
        //查询借阅图书总册
        public string BookSum()
        {
            string sqlSum = @"select COUNT(*) from Borrow";
            //执行
            int sum = Convert.ToInt32(DBHelper.ExecuteScalar(sqlSum));

            if (sum != -1)
            {
                //显示图书总计
                return string.Format("共{0}次", sum.ToString());
            }
            else
            {
                return "查询异常";
            }
        }
        public string ReaderSum()
        {
            //在借人数汇总
            string sql = @"select count(*) from reader";

            //执行
            int iRet = Convert.ToInt32(DBHelper.ExecuteScalar(sql));

            if (iRet != -1)
            {
                return iRet.ToString() + "人";
            }
            else
            {
                return "查询异常";
            }
        }
        public string OverdueSum()
        {
            //SQL语句查询逾期图书数
            string sql = @"select count(*) from borrow where DateDiff(day,ReturnDate,GETDATE()) > 0 and GiveBackDate is null;";
            //sql查询逾期人数
            string sql2 = @"select count(*) from Reader where ReaderID in
                        (select ReaderID from Borrow where DateDiff(day,ReturnDate,GETDATE()) > 0 and GiveBackDate is null group by ReaderID);;";

            //执行
            int books = Convert.ToInt32(DBHelper.ExecuteScalar(sql));//逾期图书数
            int readers = Convert.ToInt32(DBHelper.ExecuteScalar(sql2));//逾期人数

            if (books != -1 && readers != -1)
            {
                return books.ToString() + "册" + readers.ToString() + "人";
            }
            else
            {
                return "查询异常";
            }
        }
    }
}
